package com.global.service.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.global.base.StringUtil;
import com.global.dao.DataSourceXColumnDao;
import com.global.dao.DataSourceXDao;
import com.global.dao.GridColumnDao;
import com.global.dao.QueryParameterDefinitionDao;
import com.global.dao.jdbc.GenericJdbcDao;
import com.global.model.DataSourceX;
import com.global.model.DataSourceXColumn;
import com.global.model.GridColumn;
import com.global.model.QueryParameterDefinition;
import com.global.model.Who;
import com.global.service.AbstractService;
import com.global.service.DataSourceXDefinitionService;
import com.global.vo.BooleanResult;
import com.global.vo.DBColumn;
import com.global.vo.QueryParameter;
import com.global.vo.QueryType;

@Service("dataSourceXService")
public class DataSourceXServiceImpl extends AbstractService implements DataSourceXDefinitionService {
	
	@Autowired
	GenericJdbcDao jdbcDao;
	@Autowired
	DataSourceXDao dataSourceXDao;
	@Autowired
	DataSourceXColumnDao dataSourceXColumnDao;
	@Autowired
	GridColumnDao gridColumnDao;
	@Autowired
	QueryParameterDefinitionDao queryParameterDefinitionDao;
	
	/**
	 * {@inheritDoc}
	 */
	public DataSourceX registerOrUpdateDataSourceX(DataSourceX dataSourceX){
		return dataSourceXDao.saveDataSourceX(dataSourceX);
	}
	
	/**
	 * {@inheritDoc}
	 */
	public void saveDataSourceXColumns(List<DataSourceXColumn> columnList){
		dataSourceXColumnDao.saveBatchDataSourceXColumns(columnList);
	}
	
	/**
	 * {@inheritDoc}
	 */
	public void saveDataSourceXColumns(List<DataSourceXColumn> saveList, List<DataSourceXColumn> deleList){
		dataSourceXColumnDao.saveBatchDataSourceXColumns(saveList, deleList);
	}

	/**
	 * {@inheritDoc}
	 */
	public BooleanResult registerOrUpdateDataSourceXColumns(DataSourceX dataSourceX, Who who) {
		List<String> messageList = new ArrayList<String>();
		
		if(dataSourceX==null){
			messageList.add("数据源为空，则无法更新");
			return new BooleanResult(false, StringUtil.getString(messageList));
		}
		
		dataSourceX = registerOrUpdateDataSourceX(dataSourceX);
		//刷新（从DB中获取）数据源字段
		List<DataSourceXColumn> dataSourceXColumnList = getDataSourceXColumnList(dataSourceX);
		dataSourceX.setDataSourceXColumns(dataSourceXColumnList);
		
		//将SQL对应的元数据检索出来
		List<DBColumn> dbColumnList = new ArrayList<DBColumn>();
		if(dataSourceX.getQueryType().equalsIgnoreCase(QueryType.QueryType_StaticSQL)){
			dbColumnList = jdbcDao.getStaticSQLMetaData(dataSourceX.getSql());
		}else if(dataSourceX.getQueryType().equalsIgnoreCase(QueryType.QueryType_NamedParameterSQL)){
			List<QueryParameter> namedParameters = new ArrayList<QueryParameter>();
			for(int i=0; i < dataSourceX.getFixedParameterDefinitions().size(); i++){
				QueryParameterDefinition qPDf = dataSourceX.getFixedParameterDefinitions().get(i);
				if(qPDf.isFixed()){
					namedParameters.add(new QueryParameter(qPDf.getName(), null));
				}
			}
			dbColumnList = jdbcDao.getNamedParameterSQLMetaData(dataSourceX.getSql(), namedParameters);
		}
		
		if(dbColumnList.isEmpty()){
			log.equals(jdbcDao.getErrorMessage());
			return new BooleanResult(false, jdbcDao.getErrorMessage());
		}
		
		//需要删除的数据源字段清单
		List<DataSourceXColumn> deleteDataSourceXColumns = new ArrayList<DataSourceXColumn>();
		//需要新增或者修改或者删除不了的数据源字段清单
		List<DataSourceXColumn> newDataSourceXColumnList = new ArrayList<DataSourceXColumn>();		
		
		List<String> deleteNameList = new ArrayList<String>();
		
		//数据源字段集为空，则表明为新注册数据源
		if(dataSourceXColumnList==null || dataSourceXColumnList.isEmpty()){			
			for(DBColumn c : dbColumnList){
				DataSourceXColumn dataSourceXColumn = new DataSourceXColumn(dataSourceX);
				dataSourceXColumn.setAlias(c.getAlias());
				dataSourceXColumn.setDbColumnTypeName(c.getColumnTypeName());
				dataSourceXColumn.setDescription(c.getDescription());
				dataSourceXColumn.setDisplayIndex(c.getDisplayIndex());
				dataSourceXColumn.setWho(who);
				
				newDataSourceXColumnList.add(dataSourceXColumn);
			}
		}else{
			List<DataSourceXColumn> activeDataSourceXColumns = new ArrayList<DataSourceXColumn>();
			//设置合法的数据源字段清单
			activeDataSourceXColumns.addAll(dataSourceXColumnList);
			
			//字段没有被GridColumn绑定，则直接删除; 如果被绑定，则提示用户先解除GridColumn的绑定，再手动删除
			for(DataSourceXColumn d : dataSourceXColumnList){
				boolean isExist = false;
				for(DBColumn c : dbColumnList){
					if(d.getAlias().equalsIgnoreCase(c.getAlias())){
						isExist = true;
						break;
					}
				}
				if(!isExist){
					//将不合法的字段移除
					activeDataSourceXColumns.remove(d);
					
					List<GridColumn> gList = gridColumnDao.getGridColumnList(d);
					if(gList.isEmpty()){
						deleteNameList.add(d.getAlias());						
						deleteDataSourceXColumns.add(d);
					}else{
						d.setDisplayIndex(0);
						newDataSourceXColumnList.add(d);
						
						String nameList = "";
						for(GridColumn g : gList){
							nameList = nameList + "," + "Grid: " + g.getGrid().getDescription() + " - GridColumn: " + g.getLabel();
						}
						String msg = "数据源字段【" + d.getAlias() + "】已经失效，需要删除，但由于被Grid字段【" + StringUtils.strip(nameList, ",") + "】绑定了，则不能自动删除，请先解除绑定，再删除该数据源字段！";
						messageList.add(msg);
					}
				}
			}
			
			//更新数据源，即将新增的数据源字段保存下来	，存在的字段则更新
			for(DBColumn c : dbColumnList){
				DataSourceXColumn dataSourceXColumn = null;
				for(DataSourceXColumn d : activeDataSourceXColumns){
					if(c.getAlias().equalsIgnoreCase(d.getAlias())){
						dataSourceXColumn = d;
						break;
					}
				}

				if(dataSourceXColumn==null){					
					dataSourceXColumn = new DataSourceXColumn(dataSourceX);
				}

				dataSourceXColumn.setAlias(c.getAlias());
				dataSourceXColumn.setDbColumnTypeName(c.getColumnTypeName());
				dataSourceXColumn.setDescription(c.getDescription());
				dataSourceXColumn.setDisplayIndex(c.getDisplayIndex());
				dataSourceXColumn.setWho(who);
				
				newDataSourceXColumnList.add(dataSourceXColumn);
			}			
		}
		
		//注册（更新）数据源
		saveDataSourceXColumns(newDataSourceXColumnList, deleteDataSourceXColumns);

		//同步缓存中的dataSourceX的字段清单
		dataSourceX.setDataSourceXColumns(newDataSourceXColumnList);
		
		if(!deleteDataSourceXColumns.isEmpty()){
			messageList.add("由于字段【" + StringUtil.getString(deleteNameList, ",") + "】在新数据源中已经不存在，所以被自动删除！");
		}
		
		return new BooleanResult(true, StringUtil.getString(messageList));
	}
	
	/**
	 * {@inheritDoc}
	 */
	public void registerOrUpdateQueryParameterDefinitions(List<QueryParameterDefinition> QueryParameterDefinitions) {
		queryParameterDefinitionDao.saveBatchQueryParameterDefinitions(QueryParameterDefinitions);
	}
	
	/**
	 * {@inheritDoc}
	 */
	public List<QueryParameterDefinition> getQueryParameterDefinitions(DataSourceX dataSourceX){
		return queryParameterDefinitionDao.getQueryParameterDefinitionList(dataSourceX);
	}
	
	//1、参数如果避免重名，即固定参数与临时参数
	
	/**
	 * {@inheritDoc}
	 */
	public List<DataSourceX> getAllDataSourceXList(){
		return dataSourceXDao.getAll();
	}
	
	/**
	 * {@inheritDoc}
	 */
	public List<DataSourceXColumn> getDataSourceXColumnList(DataSourceX dataSourceX){
		return dataSourceXColumnDao.getDataSourceXColumnList(dataSourceX);
	}
	
	/**
	 * {@inheritDoc}
	 */
	public BooleanResult deleteDataSourceX(DataSourceX dataSourceX){
		if(dataSourceX!=null && dataSourceX.getId()!=null){
			if(dataSourceX.getDataSourceXColumns().isEmpty()){
				dataSourceXDao.remove(dataSourceX.getId());
			}else{
				return new BooleanResult(false, "数据源中存在字段，则无法删除！");
			}
			
			return new BooleanResult(true);
		}else{
			return new BooleanResult(false, "数据源为空，则无法删除！");
		}		
	}
	
	/**
	 * {@inheritDoc}
	 */
	public BooleanResult deleteDataSourceXColumn(DataSourceXColumn dataSourceXColumn){
		ArrayList<DataSourceXColumn> list = new ArrayList<DataSourceXColumn>();
		list.add(dataSourceXColumn);
		
		return deleteBatchDataSourceXColumns(list);
	}
	
	/**
	 * {@inheritDoc}
	 */
	public BooleanResult deleteBatchDataSourceXColumns(List<DataSourceXColumn> dataSourceXColumnList){
		List<String> messageList = new ArrayList<String>();
		
		if(dataSourceXColumnList!=null){			
			List<GridColumn> gList = gridColumnDao.getGridColumnList(dataSourceXColumnList);
			if(gList.isEmpty()){
				dataSourceXColumnDao.deleteBatchDataSourceXColumns(dataSourceXColumnList);
				
				return new BooleanResult(true);
			}else{
				for(DataSourceXColumn d : dataSourceXColumnList){
					String invalidateNameList = "";
					for(GridColumn g : gList){
						if(g.getDataSourceXColumn().equals(d)){
							invalidateNameList = invalidateNameList + "," + "Grid: " + g.getGrid().getDescription() + " - GridColumn: " + g.getLabel();
						}
					}
					if(StringUtils.isNotBlank(invalidateNameList)){
						messageList.add("数据源字段【" + d.getAlias() + "】已经被Grid字段【" + StringUtils.strip(invalidateNameList, ",") + "】绑定了，则不能自动删除，请先解除绑定，再手动删除该数据源字段！");
					}					
				}
			}
		}else{
			messageList.add("数据源字段清单为空，则无法删除！");
		}

		return new BooleanResult(false,  StringUtil.getString(messageList));
	}
	
	/**
	 * {@inheritDoc}
	 */
	public BooleanResult checkDataSourceXColumn(DataSourceXColumn dataSourceXColumn){
		List<String> messageList = new ArrayList<String>();
		
		boolean isExist = false;
		boolean isSuccess = false;
		
		if(dataSourceXColumn!=null){
			DataSourceX dataSourceX = dataSourceXColumn.getDataSourceX();
			
			if(dataSourceX!=null){
				List<DBColumn> dbColumnList = new ArrayList<DBColumn>();
				
				if(dataSourceX.getQueryType().equalsIgnoreCase(QueryType.QueryType_StaticSQL)){
					dbColumnList = jdbcDao.getStaticSQLMetaData(dataSourceX.getSql());
				}else if(dataSourceX.getQueryType().equalsIgnoreCase(QueryType.QueryType_NamedParameterSQL)){
					List<QueryParameter> namedParameters = new ArrayList<QueryParameter>();
					for(int i=0; i < dataSourceX.getFixedParameterDefinitions().size(); i++){
						QueryParameterDefinition queryParameterDf = dataSourceX.getFixedParameterDefinitions().get(i);
						if(queryParameterDf.isFixed()){
							namedParameters.add(new QueryParameter(queryParameterDf.getName(), null));
						}
					}
					dbColumnList = jdbcDao.getNamedParameterSQLMetaData(dataSourceX.getSql(), namedParameters);
				}
				
				for(DBColumn dc : dbColumnList){
					boolean isPass = true;
					
					if(dc.getAlias().equalsIgnoreCase(dataSourceXColumn.getAlias())){
						isExist = true;
						
						if(!dc.getColumnTypeName().equalsIgnoreCase(dataSourceXColumn.getDbColumnTypeName())){
							isPass = false;
							messageList.add("数据源字段【" + dataSourceXColumn.getAlias() + "】的数据类型应该为【" + dc.getColumnTypeName() + "】！");
						}
						if(!dc.getDescription().equalsIgnoreCase(dataSourceXColumn.getDescription())){
							isPass = false;
							messageList.add("数据源字段【" + dataSourceXColumn.getAlias() + "】的描述应该为【" + dc.getDescription() + "】！");
						}
						if(dc.getDisplayIndex()!=dataSourceXColumn.getDisplayIndex()){
							isPass = false;
							messageList.add("数据源字段【" + dataSourceXColumn.getAlias() + "】的排列位置应该为【" + dc.getDisplayIndex() + "】！");
						}
						
						isSuccess = isPass;
						
						break;
					}
				}
				if(!isExist){
					messageList.add("数据源字段【" + dataSourceXColumn.getAlias() + "】在数据源【" + dataSourceX.getDescription() + "】中不存在！");
				}
			}else{
				messageList.add("数据源字段【" + dataSourceXColumn.getAlias() + "】没有对应的数据源，则无法校验！");
			}
		}else{
			messageList.add("数据源字段为空，则无法校验！");
		}
		
		return new BooleanResult(isSuccess, StringUtil.getString(messageList));
	}
	
	/**
	 * {@inheritDoc}
	 */
	public BooleanResult checkDataSourceXColumns(List<DataSourceXColumn> dataSourceXColumnList){
		List<String> messageList = new ArrayList<String>();
		boolean isSuccess = false;
		
		if(dataSourceXColumnList!=null){
			for(DataSourceXColumn dc : dataSourceXColumnList){
				BooleanResult checkDataSourceXColumn = checkDataSourceXColumn(dc);
				messageList.add(checkDataSourceXColumn.getMessage());
				
				if(!checkDataSourceXColumn.getResult()){
					isSuccess = false;
				}
			}
		}else{
			messageList.add("数据源字段集合为空，则无法校验！");
		}
		
		return new BooleanResult(isSuccess, StringUtil.getString(messageList));
	}
	
	/**
	 * {@inheritDoc}
	 */
	public BooleanResult checkDataSourceX(DataSourceX dataSourceX){
		List<String> messageList = new ArrayList<String>();
		boolean isSuccess = true;
		
		if(dataSourceX!=null){
			List<DataSourceXColumn> dataSourceXColumnList = dataSourceXColumnDao.getDataSourceXColumnList(dataSourceX);
			if(dataSourceXColumnList.isEmpty()){
				return new BooleanResult(true, "数据源【" + dataSourceX.getDescription() + "】没有配置数据源字段！");
			}
			
			List<DBColumn> dbColumnList = new ArrayList<DBColumn>();
			
			if(dataSourceX.getQueryType().equalsIgnoreCase(QueryType.QueryType_StaticSQL)){
				dbColumnList = jdbcDao.getStaticSQLMetaData(dataSourceX.getSql());
			}else if(dataSourceX.getQueryType().equalsIgnoreCase(QueryType.QueryType_NamedParameterSQL)){
				List<QueryParameter> namedParameters = new ArrayList<QueryParameter>();
				for(int i=0; i < dataSourceX.getFixedParameterDefinitions().size(); i++){
					QueryParameterDefinition queryParameterDf = dataSourceX.getFixedParameterDefinitions().get(i);
					if(queryParameterDf.isFixed()){
						namedParameters.add(new QueryParameter(queryParameterDf.getName(), null));
					}
				}
				dbColumnList = jdbcDao.getNamedParameterSQLMetaData(dataSourceX.getSql(), namedParameters);
			}
			
			List<String> notExistColumnNameList = new ArrayList<String>();
			for(DataSourceXColumn d : dataSourceXColumnList){
				boolean isExist = false;
				boolean isPass = true;	
				
				for(DBColumn dbColumn : dbColumnList){
					if(dbColumn.getAlias().equalsIgnoreCase(d.getAlias())){
						isExist = true;
						
						if(!dbColumn.getColumnTypeName().equalsIgnoreCase(d.getDbColumnTypeName())){
							isPass = false;
							messageList.add("数据源字段【" + d.getAlias() + "】的数据类型应该为【" + dbColumn.getColumnTypeName() + "】！");
						}
						if(!dbColumn.getDescription().equalsIgnoreCase(d.getDescription())){
							isPass = false;
							messageList.add("数据源字段【" + d.getAlias() + "】的描述应该为【" + dbColumn.getDescription() + "】！");
						}
						if(dbColumn.getDisplayIndex()!=d.getDisplayIndex()){
							isPass = false;
							messageList.add("数据源字段【" + d.getAlias() + "】的排列位置应该为【" + dbColumn.getDisplayIndex() + "】！");
						}
						
						break;
					}
				}				
				if(!isExist){
					notExistColumnNameList.add(d.getAlias());
				}else{
					if(!isPass){
						isSuccess = false;
					}
				}
			}
			
			if(!notExistColumnNameList.isEmpty()){
				isSuccess = false;
				messageList.add("数据源【" + dataSourceX.getDescription() + "】配置不合法，即数据源字段【" + StringUtil.getString(notExistColumnNameList, ",") + "】在数据源中不存在！");
			}
			
			List<String> newColumnNameList = new ArrayList<String>();
			for(DBColumn dbColumn : dbColumnList){
				boolean isExist = false;
				
				for(DataSourceXColumn d : dataSourceX.getDataSourceXColumns()){
					if(dbColumn.getAlias().equalsIgnoreCase(d.getAlias())){
						isExist = true;
						
						break;
					}
				}
				if(!isExist){
					newColumnNameList.add(dbColumn.getAlias());
				}
			}
			if(!newColumnNameList.isEmpty()){
				isSuccess = false;
				messageList.add("数据源【" + dataSourceX.getDescription() + "】发生了变化（即新增了【" + newColumnNameList.size() + "】个字段【" + StringUtil.getString(newColumnNameList, ",") + "】），需要同步数据源字段！");
			}
		}else{
			messageList.add("数据源为空，则无法校验！");
		}
		
		return new BooleanResult(isSuccess, StringUtil.getString(messageList));
	}
}
